
/**
	AUTHOR		: VEERU
	DATE		: JUNE 6th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO INSERT THE PART NUMBER.
	USAGE  		: EXEC SP_INSERT_PARTNUMBER 'ECTACO INC.','3DM-32MM-A001-EC5000','TEST9135', 'ECTACO.SAT.32MB.FLAGSTAFF REV B/CIPHER 4','EC5000', 'SOURCE ELECTRONICS'	
*/



ALTER PROC SP_INSERT_PARTNUMBER

@CUSTOMER_NAME VARCHAR( 65 ),
@PART_NUMBER   VARCHAR( 50 ),
@OPN           VARCHAR( 50 ),
@P_COMMENTS    VARCHAR( 300 ),
@P_PROGRAMCODE VARCHAR( 50 ),
@P_CRC_CODE    VARCHAR( 50 ),
@P_ALGORITHM   VARCHAR( 50 ),
@V_NAME        VARCHAR( 50 )


AS
BEGIN

	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @DB_CUSTOMER_ID INT
	DECLARE @DB_VENDOR_ID INT
	DECLARE @DB_LOCATION_ID INT
	DECLARE @DB_PARENT_PRODUCT_ID INT
	DECLARE @OPN_PROCESSSTEP_ID INT
	DECLARE @PROCESS_TYPE VARCHAR(50)
	DECLARE @PROCESS_PARAMETER VARCHAR(50)
	DECLARE @PARAMETER_UNITS VARCHAR(50) 
	DECLARE @PARAMETER_VALUE VARCHAR(50)

	--Creating an entry in the Process_Step Table for getting the new processstep_id for new Finished Good.
	INSERT INTO PROCESS_STEP( PS_TYPE, PS_TYPE_NAME, TIMESTAMP ) VALUES( 'FG', 'Finished Goods', CURRENT_TIMESTAMP )
	SET @FG_PROCESSSTEP_ID    = ( SELECT @@IDENTITY )

	--Getting the Process Step Id and Parent Product Id of OPN (i.e. Blank FGI)
	SELECT @OPN_PROCESSSTEP_ID = P.PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID = P_PARENT_PRODUCT_ID  FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'Blank FGI' AND PART_NUMBER = @OPN

	-- Getting the Customer Id for the Product 
	SELECT @DB_CUSTOMER_ID  = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME

	--Adding an entry for the New Finished Good in the Product Table.
	INSERT INTO PRODUCT( PART_NUMBER, PROCESSSTEP_ID, P_PARENT_PRODUCT_ID, P_MARKETING_PART_NUMBER, P_COMMENTS, TIMESTAMP, IS_APPROVED, P_PROGRAMCODE, P_ALGORITHM, P_CRC_CODE, IS_PRODUCT_SETUP_FG )
	VALUES ( @PART_NUMBER, @FG_PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID, @PART_NUMBER, @P_COMMENTS, CURRENT_TIMESTAMP, 1, @P_PROGRAMCODE, @P_ALGORITHM, @P_CRC_CODE, 1 )

	-- INSERT VALUES TO FG CUSTOMER MAPPING TABLE
	INSERT INTO FG_CUSTOMER_MAP_TABLE ( ProcessStep_ID, Customer_Id ) VALUES ( @FG_PROCESSSTEP_ID, @DB_CUSTOMER_ID )
	
	--If Algorithm is not in the System, Placing it in the System.
	IF ( @P_ALGORITHM IS NOT NULL AND LEN(LTRIM(RTRIM( @P_ALGORITHM ) ) )> 0 )
	BEGIN
		IF ( ( SELECT COUNT(*) FROM FINISHED_GOODS_ALGORITHM_TABLE WHERE ALGORITHM_NAME = @P_ALGORITHM ) = 0 ) 
		BEGIN
			INSERT INTO FINISHED_GOODS_ALGORITHM_TABLE( ALGORITHM_NAME ) VALUES( @P_ALGORITHM )
		END
	END

	--Getting the Finished Good System Parameters and Placing them in PROCESS_PARAMETER Table for this New Finished Good
	DECLARE SYS_PARAM_CURSOR CURSOR FOR
			SELECT PROCESS_TYPE, PROCESS_PARAMETER, PARAMETER_UNITS, PARAMETER_VALUE FROM SYSTEM_PARAMETER_TABLE WHERE PROCESS_TYPE = 'Finished Goods'
	OPEN SYS_PARAM_CURSOR

	FETCH NEXT FROM SYS_PARAM_CURSOR INTO @PROCESS_TYPE, @PROCESS_PARAMETER, @PARAMETER_UNITS, @PARAMETER_VALUE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( ( SELECT COUNT(*) FROM  PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID AND PARAMETER_KEY = @PROCESS_PARAMETER ) = 0 )
		BEGIN
			INSERT INTO PROCESS_PARAMETER( PROCESSSTEP_ID, PARAMETER_KEY, PARAMETER_UNITS, PARAMETER_VALUE ) VALUES 
			                	     ( @FG_PROCESSSTEP_ID, @PROCESS_PARAMETER, @PARAMETER_UNITS, @PARAMETER_VALUE )
		END		
		SET @PROCESS_TYPE = NULL 
		SET @PROCESS_PARAMETER = NULL  
		SET @PARAMETER_UNITS = NULL  
		SET @PARAMETER_VALUE = NULL 
		FETCH NEXT FROM SYS_PARAM_CURSOR INTO @PROCESS_TYPE, @PROCESS_PARAMETER, @PARAMETER_UNITS, @PARAMETER_VALUE
	END
	CLOSE SYS_PARAM_CURSOR
	DEALLOCATE SYS_PARAM_CURSOR

	--Mapping the OPN and Finished Good.
	INSERT INTO FG_TO_FGI_MAP_TABLE( FG_PROCESS_STEP_ID, FGI_PROCESS_STEP_ID, BASE_PART_PRODUCT_ID ) VALUES
				       ( @FG_PROCESSSTEP_ID, @OPN_PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID )
	--Mapping the OPN and Finished Good.
	INSERT INTO PROCESS_STEP_TRAVERSE_TABLE ( PROCESSSTEP_ID, NEXT_PROCESSSTEP_ID, PARENT_PRODUCT_ID ) VALUES 
				       ( @OPN_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID )

	--Mapping the Vendor Locations for this Finished Good Part Number.
	SELECT @DB_VENDOR_ID = VENDOR_ID FROM VENDOR WHERE V_NAME = @V_NAME
	DECLARE LOCATION_CURSOR CURSOR FOR SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @DB_VENDOR_ID AND L_TYPE = 'Finished Goods'
	OPEN LOCATION_CURSOR
	FETCH NEXT FROM LOCATION_CURSOR INTO @DB_LOCATION_ID 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO PROCESS_STEP_LOCATION_TABLE( PROCESSSTEP_ID, LOCATION_ID, PSLT_PRIORITY, PSLT_SPLIT, PSLT_CAPACITY, PSLT_COST_NORMAL, PSLT_COST_EXPRESS, PSLT_CYCLE_TIME_NORMAL, PSLT_CYCLE_TIME_EXPRESS, PSLT_EXPECTED_YIELD, PSLT_ASY_RAW_MATERIAL_SUPPLY,PSLT_ASY_MIN_LEVEL, PSLT_ASY_MAX_LEVEL,PSLT_LASER_REPAIRED, PSLT_LASER_REPAIRED_COST, PSLT_TIMESTAMP ) VALUES
						       ( @FG_PROCESSSTEP_ID, @DB_LOCATION_ID, 1, 0.0, -1, 0.0, 0.0, -1, -1, 0.0, 1, -1, -1, 1, 0.0, CURRENT_TIMESTAMP )
		SET @DB_LOCATION_ID = NULL
		FETCH NEXT FROM LOCATION_CURSOR INTO @DB_LOCATION_ID 
	END
	CLOSE LOCATION_CURSOR
	DEALLOCATE LOCATION_CURSOR

END
